package com.xiaoy.core.report.excel;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.persistence.Column;

import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.springside.modules.orm.Page;

import com.xiaoy.core.report.IReportAdapter;
import com.xiaoy.page.PageReport;

public class ExcelPoiSerivce extends IReportAdapter {

	private int sequence; //当前行标
	
	@Override
	public void importResource() throws Exception {

	}

	public OutputStream exportResource(String path) throws Exception {

		List<DataTable> dataTables = getDataTables();

		for (DataTable table : dataTables) {
			table.setColumns(getDBColumnsByTablename(table.getName()));
		}

		HSSFWorkbook workbook = new HSSFWorkbook(); // 产生工作簿对象
		HSSFSheet sheet = workbook.createSheet(""); // 产生工作表对象
		// 设置第一个工作表的名称为firstSheet
		// 为了工作表能支持中文，设置字符编码为UTF_16
		workbook.setSheetName(0, "表结构");

		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
		cellStyle.setWrapText(true);// 指定单元格自动换行
		cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		short b = HSSFCellStyle.BORDER_THIN;
		cellStyle.setBorderTop(b);
		cellStyle.setBorderBottom(b);
		cellStyle.setBorderLeft(b);
		cellStyle.setBorderRight(b);

		int startLine = 2;
		int currentLine = startLine; // 当前行
		int currentCol = 0; // 当前列

		for (int i = 0; i < dataTables.size(); i++) {

			DataTable table = dataTables.get(i);

			HSSFRow row = sheet.createRow(currentLine++);

			// row.setRowStyle(new Hsscellstyle());
			// 产生第一个单元格,设置单元格内容为字符串型
			// HSSFCell cell =
			// row.createCell(currentCol,HSSFCell.CELL_TYPE_STRING);
			// 为了能在单元格中写入中文，设置字符编码为UTF_16。
			// cell.setEncoding();
			// 往第一个单元格中写入信息
			// cell.setCellValue(table.getName());

			// row.setRowStyle(border);

			row.createCell(currentCol, HSSFCell.CELL_TYPE_STRING).setCellValue(
					"表名");
			row.createCell(currentCol + 1, HSSFCell.CELL_TYPE_STRING)
					.setCellValue(table.getName());

			row.createCell(currentCol + 2, HSSFCell.CELL_TYPE_STRING)
					.setCellValue("注解");
			row.createCell(currentCol + 3, HSSFCell.CELL_TYPE_STRING)
					.setCellValue(table.getInfo());

			// currentLine = currentLine + i;
			List<Column> columns = table.getColumns();

			HSSFRow row2 = sheet.createRow(currentLine++);

			for (int j = 0; j < fieldsInfo.length; j++) {

				HSSFCell cell = row2.createCell(currentCol + j,
						HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(fieldsInfo[j]);
			}
			// row2.setRowStyle(cellStyle);
			//
			for (int j = 0; j < columns.size(); j++) {

				Column column = columns.get(j);
				HSSFRow row_column = sheet.createRow(currentLine++);
				// {"名称","注解","长度","是否null","类型","默认值"};
				row_column.createCell(currentCol, HSSFCell.CELL_TYPE_STRING)
						.setCellValue(column.getName());
				row_column
						.createCell(currentCol + 1, HSSFCell.CELL_TYPE_STRING)
						.setCellValue(column.getInfo());
				row_column
						.createCell(currentCol + 2, HSSFCell.CELL_TYPE_STRING)
						.setCellValue(column.getLength());
				row_column
						.createCell(currentCol + 3, HSSFCell.CELL_TYPE_STRING)
						.setCellValue(column.getIsNull());
				row_column
						.createCell(currentCol + 4, HSSFCell.CELL_TYPE_STRING)
						.setCellValue(column.getType());
				row_column
						.createCell(currentCol + 5, HSSFCell.CELL_TYPE_STRING)
						.setCellValue(column.getDefaultValue());
			}
			sheet.createRow(currentLine++);
		}

		OutputStream fOut = new FileOutputStream(path);
		workbook.write(fOut);
		// fOut.flush();
		// fOut.close();
		return fOut;
	}

	protected void createPage (HSSFWorkbook workbook,PageReport page) {
		
		String sheetname = "";
		HSSFSheet sheet;
		for (int i = 1;i<page.getTotalPages();i++) {
			sheetname = "  ("+i+"/"+page.getTotalPages()+") ";
			sheet = workbook.createSheet(sheetname);
			
			// 获得每一页的数据
			
		}
		
	}
	
	
	
	protected void createBody (HSSFSheet sheet,List<Map<String,Object>> datas) {
		
		for (Map<String,Object> o:datas) {
			createRow(sheet,o);
		}
		
	}
	
	protected void createRow (HSSFSheet sheet,Map<String,Object>data) {
		HSSFRow row = sheet.createRow(getSequence());
		
		int colIndex = 0;
		for (Map.Entry<String, Object>e:data.entrySet()) {
			String key = e.getKey();
			Object value = e.getValue();
			createCell(row,value,colIndex++);
		}
	}
	
	protected void createCell (HSSFRow row,Object value,int colIndex) {
		
		HSSFCell cell = row.createCell(colIndex);

		int type = HSSFCell.CELL_TYPE_STRING;
		if (value instanceof Number) {
			type = HSSFCell.CELL_TYPE_NUMERIC;
		} else if (value instanceof Date) {
			// type = HSSFCell.CELL_TYPE_NUMERIC;
		}
		cell.setCellType(type);

		if (value instanceof Number) {
			Number num = (Number) value;
			cell.setCellValue(num.doubleValue());
		} else if (value instanceof Date) {
			cell.setCellValue((Date) value);
		} else if (value instanceof Calendar) {
			cell.setCellValue((Calendar) value);
		} else {
			cell.setCellValue(new HSSFRichTextString(escapeColumnValue(value)));
		}
	}
	
	protected int getSequence () {
		return this.sequence++;
	}

	protected String escapeColumnValue(Object rawValue) {
		if (rawValue == null) {
			return null;
		}
		String returnString = ObjectUtils.toString(rawValue);
		// escape the String to get the tabs, returns, newline explicit as \t \r
		// \n
		returnString = StringEscapeUtils.escapeJava(StringUtils
				.trimToEmpty(returnString));
		// remove tabs, insert four whitespaces instead
		returnString = StringUtils.replace(StringUtils.trim(returnString),
				"\\t", "    ");
		// remove the return, only newline valid in excel
		returnString = StringUtils.replace(StringUtils.trim(returnString),
				"\\r", " ");
		// unescape so that \n gets back to newline
		returnString = StringEscapeUtils.unescapeJava(returnString);
		return returnString;
	}
}
